package com.ultrapower.ctool.db;


import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class SqlHelpDemo extends SQLiteOpenHelper {
    /*
	 * 创建语句
	 */
    // 创建用户表
    String createUserTable = "create table user(_id int auto_increment,username char(40),"
            + "password char(20),role char(40),primary key('_id'));";
    // 创建生产经营单位表
    String createJy = "create table jingying(_id int auto_increment,"
            + "grid_name char(256),company char(256),type_1 char(256)," +
            "type_2 char(256),address char(1024),grid_user char(256)," +
            "code int,manager char(256),phone char(256),primary key('_id'));";
    //创建网格事件表
    String createGridCase = "create table grid_case(_id int auto_increment,"
            + "type char(256),step char(256),company_id char(256),name char(256)," +
            "address char(1024),ggbh char(256),wgbh char(256),wgry char(256)," +
            "lrr char(256),time char(256),primary key('_id'));";
    //创建日常巡查表+专项巡查表
    String createRiChang = "create table ri_chang(_id int auto_increment,"
            + "xcsj char(256),scyydw char(256),wgry char(256),wgbh char(256)," +
            "lrr char(256),lrsj char(256),xgr char(256),xgsj char(256),primary key('_id'));";
    //创建网格信息表
    String createGridInfo = "create table grid_info(_id int auto_increment,"
            + "grid_name char(256),sqmc char(256),address char(256),longitude char(256)," +
            "latitude char(256),fzr_name char(256),fzr_phone char(256),primary key('_id'));";

    // 定义用户表插入语句
    String insertUser = "insert into user(_id,username,password,role) values(?,?,?,?)";
    // 定义生产经营单位表插入语句
    String insertJingying = "insert into jingying values(?,?,?,?,?,?,?,?,?,?);";
    //定义网格事件表插入语句
    String insertGridCase = "insert into grid_case values(?,?,?,?,?,?,?,?,?,?,?);";
    //定义日常巡查表+专项巡查表插入语句
    String insertRiChang = "insert into ri_chang values(?,?,?,?,?,?,?,?,?);";
    //定义网格信息表插入语句
    String insertGridInfo= "insert into grid_info values(?,?,?,?,?,?,?,?);";


    public SqlHelpDemo(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建各表
        db.execSQL(createUserTable);
        db.execSQL(createJy);
        db.execSQL(createGridCase);
        db.execSQL(createRiChang);
        db.execSQL(createGridInfo);

        // 插入测试data
        String[] insertUser1 = {"0","admin","123456","normal"};
        String[] insertJingying1 = {"0","西庄村第二网格","111","公司",
                "公司","北京","西庄村第二网格用户","11","4","111"};
        String[] insertGridCase1 = {"0","事件类型 ","事件步骤 ",
                "单位编号","事件名","事件发生地址","关联的巡查表编号","网格编号","网格人员 ","录入人","录入时间"};
        String[] insertRiChang1 = {"0","巡查时间","生产运营单位","网格人员","网格编号","录入人","录入时间",
        "修改人","修改事件"};
        String[] insertW1 = {"0","网格名称","社区名称","地址","经度","纬度","负责人姓名",
                "负责人电话"};
        db.execSQL(insertUser, insertUser1);
        db.execSQL(insertJingying, insertJingying1);
        db.execSQL(insertGridCase, insertGridCase1);
        db.execSQL(insertRiChang, insertRiChang1);
        db.execSQL(insertGridInfo, insertW1);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}
